﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mapeo;
using System.Data;
using NHibernate;
using MySql.Data.MySqlClient;
using BE;
using NHibernate.Criterion;
namespace DAL
{
    public class MatricularAlumnos_DAL:Controlador
    {

        public DataTable GenerarCSV(String Semestre, String Grupo, String Ciclo, String CodigoAlumno, String NombreGrupo, String NombreCiclo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "CursorMatricularAlumnosMoodle";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inSemestre", Semestre);
                    SqlCmd.Parameters.AddWithValue("@inGrupo", Grupo);
                    SqlCmd.Parameters.AddWithValue("@inCiclo", Ciclo);
                    SqlCmd.Parameters.AddWithValue("@inCodigoAlumno", CodigoAlumno);
                    SqlCmd.Parameters.AddWithValue("@inNombreGrupo", NombreGrupo);
                    SqlCmd.Parameters.AddWithValue("@inNombreCiclo", NombreCiclo);

                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }


        public List<Periodo> ObtenerPeriodos()
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    NHibernate.ICriteria criteria = Sesion.CreateCriteria("Periodo").Add(Restrictions.Eq("MigracionMatriculaActivo", true))
                        .AddOrder(Order.Desc("Semestre"));
                    List<Periodo> listado = (List<Periodo>)criteria.List<Periodo>();
                    return listado;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }




        public DataTable ListaEspecialidadNomina(String Semestre)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ListaEspecialidadNomina";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inSemestre", Semestre);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }

        public DataTable ListaCiclosNomina(String Semestre, String Grupo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ListaCiclosNomina";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inSemestre", Semestre);
                    SqlCmd.Parameters.AddWithValue("@inGrupo", Grupo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }


        public DataTable ListaAlumnosNomina(String Semestre, String Grupo, String Ciclo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ListaAlumnosNomina";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inSemestre", Semestre);
                    SqlCmd.Parameters.AddWithValue("@inGrupo", Grupo);
                    SqlCmd.Parameters.AddWithValue("@inciclo", Ciclo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }



    }
}
